package Model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import javax.swing.JOptionPane;

public class ServicoDAO {

    /*
    int nCodServico;
    String sDescServico;
    double dValorMaoObra;
    */

    public ArrayList<ServicoBO> lista_servicos_com_filtro(String coluna_filtro, String texto_filtro) {
        Connection conexao;
        Statement executorSQL;

        try {
            conexao = ConexaoSingleton.getInstance().getConnection();
            executorSQL = conexao.createStatement();

            String SQL = "select * from tb_servico where " + coluna_filtro
                    + " like '%" + texto_filtro + "%';";

            ResultSet resultado = executorSQL.executeQuery(SQL);
            ArrayList<ServicoBO> lista = new ArrayList<ServicoBO>();
            
            while (resultado.next()) {
                ServicoBO serv = new ServicoBO(resultado.getInt("nCodServico"),
                        resultado.getString("sDescServico"),
                        resultado.getDouble("dValorMaoObra"),
                        resultado.getInt("nFlSituacao"));
                lista.add(serv);
            }

            executorSQL.close();
            return lista;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ServicosDAO.lista_servicos-Erro: " + e.getMessage());
            return new ArrayList<ServicoBO>();
        }
    }

    public ArrayList<ServicoBO> lista_servicos() {
        Connection conexao;
        Statement executorSQL;

        try {

            conexao = ConexaoSingleton.getInstance().getConnection();
            executorSQL = conexao.createStatement();

            String SQL = "select * from tb_servico;";
            ResultSet resultado = executorSQL.executeQuery(SQL);
            ArrayList<ServicoBO> lista = new ArrayList<ServicoBO>();

            while (resultado.next()) {
                ServicoBO serv = new ServicoBO(resultado.getInt("nCodServico"),
                        resultado.getString("sDescServico"),
                        resultado.getDouble("dValorMaoObra"),
                        resultado.getInt("nFlSituacao"));
                lista.add(serv);
            }
            
            executorSQL.close();
            return lista;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ServicosDAO.lista_servicos-Erro: " + e.getMessage());
            return new ArrayList<ServicoBO>();
        }
    }

    public boolean insere_servico(ServicoBO serv) {
        Connection conexao;
        Statement executorSQL;
        try {
            conexao = ConexaoSingleton.getInstance().getConnection();
            executorSQL = conexao.createStatement();
            
            String SQL = "insert into tb_servico (sDescServico,dValorMaoObra,nFlSituacao) "
                    + " values('" + serv.getsDescServico() + "',"
                    + serv.getdValorMaoObra() + ",1);";
            System.out.println("Comando SQL: " + SQL);
            executorSQL.execute(SQL);
            executorSQL.close();
            return true;
        } catch (Exception e) {
            //JOptionPane.showMessageDialog(null, "ServicosDAO.Insere_servicos-Erro: " + e.getMessage());
            JOptionPane.showMessageDialog(null, "Insira todos os Dados obrigatorios.");
            return false;
        }

    }

    public boolean exclui_servico(ServicoBO serv) {
        Connection conexao;
        Statement executorSQL;

        try {
            conexao = ConexaoSingleton.getInstance().getConnection();
            executorSQL = conexao.createStatement();

            String SQL = "delete from tb_servico where nCodServico = " + serv.getnCodServico() + ";";

            System.out.println("Comando SQL: " + SQL);
            boolean resultado = executorSQL.execute(SQL);

            executorSQL.close();
            return resultado;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ServicosDAO.Insere_servico-Erro: " + e.getMessage());
            return false;
        }
    }

    public boolean atualiza_servico(ServicoBO serv) {
        Connection conexao;
        Statement executorSQL;

        try {
            conexao = ConexaoSingleton.getInstance().getConnection();
            executorSQL = conexao.createStatement();

            String SQL = "update tb_servico set sDescServico='" + serv.getsDescServico()
                    + "', dValorMaoObra=" + serv.getdValorMaoObra()
                    + ",nFlSituacao=" + serv.getnFlSituacao()
                    + " where nCodServico = " + serv.getnCodServico() + ";";

            System.out.println("Comando SQL: " + SQL);
            boolean resultado = executorSQL.execute(SQL);

            executorSQL.close();
            return resultado;
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, "ServicosDAO.atualiza_servico-Erro: " + e.getMessage());
            return false;
        }
    }
}
